﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.IO;
using System.Text;
using System.Data.Common;

namespace DAO
{
    public partial class Connection
    {
        enum ProviderType
        {
            Oracle, SqlServer, OleDb, Odbc, MySql
        }
        ProviderType type = ProviderType.SqlServer;
        protected static string _connectionString = string.Empty;
        protected SqlConnection connection;
        protected SqlDataAdapter adapter;
        public DbProviderFactory _factory;
        protected SqlCommand command;
        protected SqlTransaction trans = null;
        protected static byte[] GetBytes(string str)
        {
            byte[] bytes = new byte[str.Length * sizeof(char)];
            System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
            return bytes;
        }
        protected static string ConvertEncoding(string text, Encoding source, Encoding dest)
        {
            byte[] utfBytes = GetBytes(text);//inEncode.
            byte[] isoBytes = Encoding.Convert(source, dest, utfBytes);
            return dest.GetString(isoBytes).Replace("\0", "");
        }
        public Connection()
        {
            //_connectionString = "";
            //connection = new SqlConnection(_connectionString);
            //command = new SqlCommand();
        }
        public static string ConnectionString
        {
            get
            {
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }
        public bool TestConnect()
        {
            bool flag = true;
            try
            {
                InitConnect();

            }
            catch
            {
                flag = false;

            }
            finally { closeConnection(); }
            return flag;
        }
        public void InitConnect()
        {
            //  CreateFactory(type);
            if (connection == null) CreateConnection(ConnectionString);
            if (command == null || command.Connection != connection) command = connection.CreateCommand();
            if (adapter == null || adapter.SelectCommand != command) adapter = CreateAdapter(command);

        }

        private SqlDataAdapter CreateAdapter(SqlCommand a_command)
        {
            //DbDataAdapter res = _factory.CreateDataAdapter();
            SqlDataAdapter res = new SqlDataAdapter(a_command);
            res.SelectCommand = a_command;

            return res;
        }

        private void CreateConnection(string connectString)
        {
            // connection = _factory.CreateConnection();           
            //connection.ConnectionString = connectString;
            connection = new SqlConnection(connectString);
        }

        private void CreateFactory(ProviderType type)
        {
            if (type == ProviderType.SqlServer) _factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
            else if (type == ProviderType.MySql) _factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
            else if (type == ProviderType.Oracle) _factory = DbProviderFactories.GetFactory("System.Data.OracleClient");
            else if (type == ProviderType.OleDb) _factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            else _factory = DbProviderFactories.GetFactory("System.Data.Odbc");
        }
        public bool SetTransaction(object oTrans)
        {
            if (oTrans is SqlTransaction)
            {
                SqlTransaction o = oTrans as SqlTransaction;
                trans = o;
                connection = o.Connection;
                command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = o;
                return true;
            }
            return false;
        }
        public object GetTransaction()
        {
            return trans;
        }
        public object BeginTransaction(ref String sErr)
        {
            try
            {
                openConnection();
                trans = connection.BeginTransaction();
                command.Transaction = trans;
                return trans;
            }
            catch (DbException ex)
            {
                sErr = ex.Message;
                return null;
            }
        }
        public bool CommitTransaction(ref String sErr)
        {
            bool res = false;
            if (trans == null) res = true;

            try
            {
                trans.Commit();
                trans = null;

                res = true;
            }
            catch (DbException ex)
            {
                sErr = ex.Message;
                trans.Rollback();
                trans = null;

            }
            finally
            {
                closeConnection();
            }
            return res;
        }
        public bool RollbackTransaction(ref String sErr)
        {
            bool res = false;
            if (trans == null) res = true;

            try
            {
                trans.Rollback();
                trans = null;
                res = true;
            }
            catch (DbException ex)
            {
                sErr = ex.Message;
                trans = null;
            }
            finally
            {
                closeConnection();
            }
            return res;
        }


        /// <summary>
        /// Thêm tham số vào command
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        protected void AddParameter(string name, object value)
        {
            AddParameter(name, value, ParameterDirection.Input);
        }
        protected object GetReturnValue(string name)
        {
            return command.Parameters[name].Value;
        }
        protected void AddParameter(string name, object value, ParameterDirection dir)
        {
            SqlParameter para = new SqlParameter();
            para.ParameterName = name;
            para.SqlValue = value;
            para.Direction = dir;
            command.Parameters.Add(para);
        }
        /*
        public static Byte[] ConvertImageToByte(Image value)
        {
            // string filename = ".\\Template\\" +Convert.ToString(DateTime.Now.ToFileTime());
            // value.Save(filename);
            // FileInfo fileInfo = new FileInfo(filename);
            // FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.ReadWrite);
            // Byte[] barrImg = new Byte[Convert.ToInt32(fileInfo.Length)];
            // int iBytesRead = fs.Read(barrImg, 0,
            //              Convert.ToInt32(fileInfo.Length));
            //// File.Delete(filename);
            // fs.Close();
            MemoryStream ms = new MemoryStream();
            value.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
            Byte[] barrImg = ms.ToArray();
            return barrImg;
        }
        public static Image ConvertByteToImage(Byte[] value)
        {
            //string filename = ".\\Template\\" + Convert.ToString(DateTime.Now.ToFileTime());
            //FileStream fs = new FileStream(filename, FileMode.CreateNew, FileAccess.ReadWrite);
            //fs.Write(value, 0, value.Length);
            //fs.Flush();
            //fs.Close();
            MemoryStream fs = new MemoryStream(value);
            Image kq = Image.FromStream(fs);
            //File.Delete(filename);
            return kq;
        }
        
        protected void AddParameterImage(string name, System.Drawing.Image value)
        {
            SqlParameter para = command.CreateParameter();
            para.ParameterName = name;
            Byte[] barrImg = ConvertImageToByte(value);
            para.SqlValue = barrImg;
            command.Parameters.Add(para);
        }*/

        /// <summary>
        /// Thêm mảng tham số vào command
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        protected void AddParameters(SqlParameter[] arrParam)
        {
            for (int i = 0; i < arrParam.Length; i++)
                command.Parameters.Add(arrParam[i]);
        }

        /// <summary>
        /// Tạo mảng tham số
        /// </summary>
        /// <param name="objEntr"></param>
        /// <returns></returns>
        protected virtual void GetParammeter(object objEntr)
        {
            return;
        }
        int Timeout = 0;
        protected virtual void InitCommand(string strCommandText, CommandType commandType = CommandType.Text)
        {
            if (command == null) connection.CreateCommand();

            command.CommandType = commandType;
            command.CommandText = strCommandText;
            //command.CommandTimeout = Timeout;
            command.Parameters.Clear();

            adapter = CreateAdapter(command);
        }
        public void closeConnection()
        {
            if (trans != null) return;
            if (connection.State != ConnectionState.Broken && connection.State != ConnectionState.Closed)
                connection.Close();
        }

        #region Execute Query
        public virtual DataTable executeSelectQuery(string sqlString)
        {
            command = connection.CreateCommand();
            command.CommandText = sqlString;
            return executeSelectQuery(command);
        }

        public virtual DataTable executeSelectQuery(SqlCommand command)
        {
            adapter = CreateAdapter(command);
            return executeSelectCollect().Tables[0];
        }
        public virtual DataTable executeSelect()
        {
            return executeSelectCollect().Tables[0];
        }
        public virtual DataTable executeSelect(SqlCommand command)
        {
            return executeSelectCollect(command).Tables[0];
        }

        public virtual DataSet executeSelectCollect(SqlCommand command)
        {
            adapter = CreateAdapter(command);
            return executeSelectCollect();
        }
        public virtual DataSet executeSelectCollect()
        {
            DataSet ds = new DataSet();

            adapter.Fill(ds);
            return ds;
        }

        public virtual IDataReader executeQuery(string sqlString)
        {
            command.CommandText = sqlString;
            return executeQuery();
        }

        public virtual IDataReader executeQuery()
        {
            return command.ExecuteReader();
        }

        public virtual int executeNonQuery(string sqlString)
        {
            command.CommandText = sqlString;
            return executeNonQuery();
        }
        public virtual int executeNonQuery()
        {
            return command.ExecuteNonQuery();
        }

        public virtual object executeScalar()
        {
            return command.ExecuteScalar();
        }
        public virtual object executeScalar(SqlCommand aCommand)
        {
            command = aCommand;
            return executeScalar();
        }
        public virtual object executeScalar(string sqlString)
        {
            command.CommandText = sqlString;
            return executeScalar();
        }
        #endregion Execute Query
        /// <summary>
        /// Chuyển một dòng dữ liệu thành một đối tượng tương ứng với lớp kế thừa
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        protected virtual object GetDataFromDataRow(DataTable dt, int i)
        {

            return null;
        }
        protected ArrayList ConvertDataSetToArrayList(DataSet dataset)
        {
            ArrayList arr = new ArrayList();
            DataTable dt = dataset.Tables[0];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr.Add(hs);

            }
            return arr;
        }
        protected ArrayList ConvertDataTableToArrayList(DataTable dt)
        {
            ArrayList arr = new ArrayList();
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr.Add(hs);
            }
            return arr;
        }
        protected Object[] ConvertDataSetToArray(DataSet dataset)
        {
            DataTable dt = dataset.Tables[0];
            Object[] arr = new Object[dt.Rows.Count];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr[i] = hs;
            }
            return arr;
        }
        protected Object[] ConvertDataTableToArray(DataTable dt)
        {
            Object[] arr = new Object[dt.Rows.Count];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object hs = GetDataFromDataRow(dt, i);
                arr[i] = hs;
            }
            return arr;
        }
        public DateTime GetDateSys()
        {
            object date = DateTime.Now;
            openConnection();
            try
            {
                date = executeScalar("sp_GetSysDate");
            }
            finally { closeConnection(); }
            return (DateTime)date;
        }

        public static bool TestConnect(string connectString)
        {
            using (SqlConnection test = new SqlConnection(connectString))
            {
                try
                {
                    test.Open();
                    test.Close();
                    return true;
                }
                catch
                {
                }
            }
            return false;
        }

        public static DataTable GetDataBases(string Server, string UserName, string Pass)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                try
                {
                    //Server=.;Database=SiteCamera;uid=sa;pwd=qawsed;Connection Lifetime=100;Connect Timeout=500
                    string connectString = String.Format("Server={0}; uid={1};pwd={2}; Connection Lifetime=100;Connect Timeout=500", Server, UserName, Pass);
                    conn.ConnectionString = connectString;
                    conn.Open();
                    DataTable dt = conn.GetSchema("Databases");
                    conn.Close();
                    return dt;
                }
                catch //(Exception ex)
                {
                    if (conn != null)
                        conn.Close();
                    return null;
                }
            }
        }



        public void openConnection()
        {
            InitConnect();
            if (connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed) connection.Open();
        }
        protected virtual void InitSPCommand(string strCommandText)
        {
            InitCommand(strCommandText, CommandType.StoredProcedure);
        }

        public DataTable SelectData(string fields, string table, ref string sErr)
        {
            try
            {
                string query = String.Format("SELECT {0} FROM {1} as [TABLE]", fields, table);
                return executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
                return null;
            }
        }
    }
}
